/* $Id: SingleClientPersonDAOImpl.java 
 * Copyright (C) 2011, TRAKHEES
 *  
 * Revision History
 *
 * Date  			Author 		    	Remarks
 * ---------------------------------------------------------------------------------
 *
 */
package ae.pcfc.etks.online.common.dao.provider;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.StructDescriptor;

import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.jboss.resource.adapter.jdbc.WrappedConnection;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;

import ae.pcfc.etks.online.common.ConstantsCommon;
import ae.pcfc.etks.online.common.dao.SingleClientPersonDAO;
import ae.pcfc.etks.online.common.domain.ClientPersonReq;
import ae.pcfc.etks.online.common.domain.ClientPersonRoleReq;
import ae.pcfc.etks.online.common.domain.TTscClientCategory;
import ae.pcfc.etks.online.common.domain.TTscClientPerson;
import ae.pcfc.etks.online.common.domain.TTscLicType;
import ae.pcfc.etks.online.common.exception.SystemException;
import ae.pcfc.etks.online.common.exception.TscDuplicateClientEmailException;
import ae.pcfc.etks.online.common.vo.ClientNameVO;
import ae.pcfc.etks.online.common.vo.SearchCriteriaPersonVO;
import ae.pcfc.etks.online.common.vo.TrakheesIdAndPINVO;
import ae.pcfc.etks.online.common.vo.TrakheesIdVO;

@Repository
public class SingleClientPersonDAOImpl extends SingleClientBaseDAO implements
		SingleClientPersonDAO {

	// It will insert the data in the TSC Client Person Table and will return
	// the Trakhees ID
	/**
	 * add
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonDAO#add(ae.pcfc.etks.online.common.domain.ClientPersonReq)
	 * 
	 */
	public String add(ClientPersonReq clientPersonReq) {

		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.SINGLE_CLIENT_PERSON_INSERT_PROC);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(new SqlParameter(
				"P_TSC_PARTNER_SYSTEM_ID", Types.VARCHAR), new SqlParameter(
				"P_CP_NAMETITLE", Types.NUMERIC), new SqlParameter("P_CP_NAME",
				Types.VARCHAR),
				new SqlParameter("P_CP_NAME_ARB", Types.VARCHAR),
				new SqlParameter("P_CP_NATIONALITY", Types.VARCHAR),
				new SqlParameter("P_CP_DOB", Types.DATE), new SqlParameter(
						"P_CP_GENDER", Types.VARCHAR), new SqlParameter(
						"P_CP_POBOX", Types.VARCHAR), new SqlParameter(
						"P_CP_POBOX_LOCATION", Types.VARCHAR),
				new SqlParameter("P_CP_ADDRESS", Types.VARCHAR),
				new SqlParameter("P_CP_ADDRESS_ARB", Types.VARCHAR),
				new SqlParameter("P_CP_HOME_TEL", Types.VARCHAR),
				new SqlParameter("P_CP_OFF_TEL", Types.VARCHAR),
				new SqlParameter("P_CP_MOBILE1", Types.VARCHAR),
				new SqlParameter("P_CP_MOBILE2", Types.VARCHAR),
				new SqlParameter("P_CP_EMAIL", Types.VARCHAR),
				new SqlParameter("P_CP_WEBSITE", Types.VARCHAR),
				new SqlParameter("P_CP_PPNO", Types.VARCHAR), new SqlParameter(
						"P_CP_PP_POI", Types.VARCHAR), new SqlParameter(
						"P_CP_PP_DOI", Types.DATE), new SqlParameter(
						"P_CP_PP_DOE", Types.DATE), new SqlParameter(
						"P_CP_VISANO", Types.NUMERIC), new SqlParameter(
						"P_CP_V_TYPE", Types.VARCHAR), new SqlParameter(
						"P_CP_V_EXPIRY", Types.DATE), new SqlParameter(
						"P_CP_EBDARA", Types.VARCHAR), new SqlParameter(
						"P_CP_CREATEDUSER", Types.VARCHAR), new SqlParameter(
						"P_CP_CREATEDTIME", Types.DATE), new SqlParameter(
						"P_CP_MODIFIEDUSER", Types.VARCHAR), new SqlParameter(
						"P_CP_MODIFIEDTIME", Types.DATE), new SqlParameter(
						"P_CP_STATUS", Types.VARCHAR), new SqlParameter(
						"P_CP_CITY", Types.VARCHAR), new SqlParameter(
						"P_CP_CITY_ARB", Types.VARCHAR), new SqlParameter(
						"P_CP_COUNTRY", Types.VARCHAR), new SqlParameter(
						"P_CP_COUNTRY_ARB", Types.VARCHAR), new SqlParameter(
						"P_CP_FAX", Types.VARCHAR), new SqlParameter(
						"P_CP_EMIRATES_ID", Types.VARCHAR), new SqlParameter(
						"P_CP_REMARKS", Types.VARCHAR), new SqlParameter(
						"P_CP_CATEGORY", Types.VARCHAR),
						new SqlParameter("P_SYS_ATTRIBUTES", Types.ARRAY),
						new SqlParameter("P_CLIENT_ROLES", OracleTypes.ARRAY),
						new SqlParameter("P_CLIENT_RELATIONSHIP", Types.ARRAY),
						new SqlOutParameter("P_CP_TRAKHEES_ID", Types.VARCHAR));
		StructDescriptor structDescriptor=null;
		ArrayDescriptor arrayDescriptor=null;
		
		//Object[] arrObj1 = null, arrObj2 = null, arrObj3 = null;
		Object[][] recObj1 = null, recObj2 = null, recObj3 = null;
		ARRAY arr1 = null, arr2=null, arr3= null;
		Connection conn = null;
		try{
			Connection jbossConn = getJdbcTemplate().getDataSource().getConnection();
			conn = ((WrappedConnection) jbossConn).getUnderlyingConnection(); 
			structDescriptor = StructDescriptor.createDescriptor(ConstantsCommon.SINGLE_CLIENT_CLIENT_ROLE_SQL_TYPE, conn);
			arrayDescriptor = ArrayDescriptor.createDescriptor(ConstantsCommon.SINGLE_CLIENT_CLIENT_ROLES_SQL_TYPE, conn);
			
			if(clientPersonReq.getClientPersonRoleReqs() != null){
				//arrObj2 = new Object[clientPersonReq.getClientPersonRoleReqs().size()];
				recObj2 = new Object[clientPersonReq.getClientPersonRoleReqs().size()][2];
				Iterator<ClientPersonRoleReq> iterator = clientPersonReq.getClientPersonRoleReqs().iterator();
				int i = 0;
				while(iterator.hasNext()){
					ClientPersonRoleReq req = iterator.next();
					recObj2[i][0] = String.valueOf(req.getCsrReqId());
					recObj2[i][1] = ConstantsCommon.ACTIVE;
					//arrObj2[i] = new STRUCT(structDescriptor, conn, recObj2[i]);
					i++;
				}

				
				arr2 = new ARRAY(arrayDescriptor, conn, recObj2);
				recObj1 = new Object[0][3];
				recObj3 = new Object[0][6];
				arr1 = new ARRAY(ArrayDescriptor.createDescriptor(ConstantsCommon.SINGLE_CLIENT_SYS_ATTRS_SQL_TYPE, conn), conn, recObj1);
				arr3 = new ARRAY(ArrayDescriptor.createDescriptor(ConstantsCommon.SINGLE_CLIENT_CLIENT_RELATIONSHIPS_SQL_TYPE, conn), conn, recObj3);
			}
		SqlParameterSource in = new MapSqlParameterSource()
				.addValue("P_TSC_PARTNER_SYSTEM_ID",
						ConstantsCommon.PARTNER_SYSTEM_ID)
				.addValue("P_CP_NAMETITLE", clientPersonReq.getCpNametitle())
				.addValue("P_CP_NAME", clientPersonReq.getCpNamEng())
				.addValue("P_CP_NAME_ARB", clientPersonReq.getCpNameArb())
				.addValue("P_CP_NATIONALITY",
						clientPersonReq.getCpNationality())
				.addValue("P_CP_DOB", clientPersonReq.getCpDob())
				.addValue("P_CP_GENDER", clientPersonReq.getCpGender())
				.addValue("P_CP_POBOX", clientPersonReq.getCpPobox())
				.addValue("P_CP_POBOX_LOCATION",
						clientPersonReq.getCpPoboxLocation())
				.addValue("P_CP_ADDRESS", clientPersonReq.getCpAddressEng())
				.addValue("P_CP_ADDRESS_ARB", clientPersonReq.getCpAddressArb())
				.addValue("P_CP_HOME_TEL", clientPersonReq.getCpHomeTel())
				.addValue("P_CP_OFF_TEL", clientPersonReq.getCpOffTel())
				.addValue("P_CP_MOBILE1", clientPersonReq.getCpMobile1())
				.addValue("P_CP_MOBILE2", clientPersonReq.getCpMobile2())
				.addValue("P_CP_EMAIL", clientPersonReq.getCpEmail())
				.addValue("P_CP_WEBSITE", clientPersonReq.getCpWebsite())
				.addValue("P_CP_PPNO", clientPersonReq.getCpPpno())
				.addValue("P_CP_PP_POI", clientPersonReq.getCpPpPoi())
				.addValue("P_CP_PP_DOI", clientPersonReq.getCpPpDoi())
				.addValue("P_CP_PP_DOE", clientPersonReq.getCpPpDoe())
				.addValue("P_CP_VISANO", clientPersonReq.getCpVisano())
				.addValue("P_CP_V_TYPE", clientPersonReq.getCpVType())
				.addValue("P_CP_V_EXPIRY", clientPersonReq.getCpVExpiry())
				.addValue("P_CP_EBDARA", clientPersonReq.getCpEbdara())
				.addValue("P_CP_CREATEDUSER",
						clientPersonReq.getCpCreatedUser())
				.addValue("P_CP_CREATEDTIME", Calendar.getInstance())
				.addValue("P_CP_MODIFIEDUSER", clientPersonReq.getCpCreatedUser())
				.addValue("P_CP_MODIFIEDTIME", Calendar.getInstance())
				.addValue("P_CP_STATUS", 'A')
				.addValue("P_CP_CITY", clientPersonReq.getCpCity())
				.addValue("P_CP_CITY_ARB", clientPersonReq.getCpCity())
				.addValue("P_CP_COUNTRY", clientPersonReq.getCpCountry())
				.addValue("P_CP_COUNTRY_ARB", clientPersonReq.getCpCountry())
				.addValue("P_CP_FAX", clientPersonReq.getCpFax())
				.addValue("P_CP_EMIRATES_ID", clientPersonReq.getCpEmiratesId())
				.addValue("P_CP_REMARKS", clientPersonReq.getCpRemarks())
				.addValue("P_CP_CATEGORY", clientPersonReq.getCpCategory())
				.addValue("P_SYS_ATTRIBUTES", arr1)
				.addValue("P_CLIENT_ROLES", arr2)
				.addValue("P_CLIENT_RELATIONSHIP", arr3);

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);

		return result.get("P_CP_TRAKHEES_ID").toString();
		} catch(Exception e){
			throw new SystemException(e);
		}
	}

	/**
	 * findSCPersonByTrakheesId
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonDAO#findSCPersonByTrakheesId(java.lang.String)
	 * 
	 */
	public TTscClientPerson findSCPersonByTrakheesId(String trakheesId) {
		@SuppressWarnings("deprecation")
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
				.withProcedureName(
						ConstantsCommon.GET_SINGLE_CLIENT_PERSON_BY_TRAKHEES_ID)
				.returningResultSet("p_RECORDSET",
						new ParameterizedRowMapper<TTscClientPerson>() {

							public TTscClientPerson mapRow(ResultSet rs,
									int rowNum) throws SQLException {
								TTscClientPerson person = new TTscClientPerson();

								person.setCpClientid(rs
										.getBigDecimal("CP_CLIENTID"));
								person.setCpNametitle(rs
										.getBigDecimal("CP_NAMETITLE"));
								person.setCpName(rs.getString("CP_NAME"));
								person.setCpNameArb(rs.getString("CP_NAME_ARB"));
								person.setCpDob(rs.getDate("CP_DOB"));
								person.setCpGender(rs.getString("CP_GENDER"));
								person.setCpPobox(rs.getString("CP_POBOX"));
								person.setCpPoboxLocation(rs
										.getString("CP_POBOX_LOCATION"));
								person.setCpAddress(rs.getString("CP_ADDRESS"));
								person.setCpAddressArb(rs
										.getString("CP_ADDRESS_ARB"));
								person.setCpHomeTel(rs.getString("CP_HOME_TEL"));
								person.setCpOffTel(rs.getString("CP_OFF_TEL"));
								person.setCpMobile1(rs.getString("CP_MOBILE1"));
								person.setCpMobile2(rs.getString("CP_MOBILE2"));
								person.setCpEmail(rs.getString("CP_EMAIL"));
								person.setCpWebsite(rs.getString("CP_WEBSITE"));
								person.setCpPpno(rs.getString("CP_PPNO"));
								person.setCpPpPoi(rs.getString("CP_PP_POI"));
								person.setCpPpDoi(rs.getDate("CP_PP_DOI"));
								person.setCpPpDoe(rs.getDate("CP_PP_DOE"));
								person.setCpVisano(rs.getString("CP_VISANO"));
								person.setCpVType(rs.getString("CP_V_TYPE"));
								person.setCpVExpiry(rs.getDate("CP_V_EXPIRY"));
								person.setCpCreateduser(rs
										.getString("CP_CREATEDUSER"));
								person.setCpCreatedtime(rs
										.getDate("CP_CREATEDTIME"));
								person.setCpModifieduser(rs
										.getString("CP_MODIFIEDUSER"));
								person.setCpModifiedtime(rs
										.getDate("CP_MODIFIEDTIME"));
								person.setCpStatus(rs.getString("CP_STATUS"));
								person.setCpCity(rs.getString("CP_CITY"));
								person.setCpCityArb(rs.getString("CP_CITY_ARB"));
								person.setCpCountry(rs.getString("CP_COUNTRY"));
								person.setCpCountryArb(rs
										.getString("CP_COUNTRY_ARB"));
								person.setCpFax(rs.getString("CP_FAX"));
								person.setCpTrakheesId(rs
										.getString("CP_TRAKHEES_ID"));
								person.setCpEmiratesId(rs
										.getString("CP_EMIRATES_ID"));
								person.setCpRemarks(rs.getString("CP_REMARKS"));
								person.setCpCategory(rs
										.getString("CP_CATEGORY"));

								return person;

							}

						});
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver
		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_TRAKHEES_ID",
				Types.VARCHAR));
		SqlParameterSource in = new MapSqlParameterSource().addValue(
				"P_TRAKHEES_ID", trakheesId);
		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		@SuppressWarnings("unchecked")
		List<TTscClientPerson> resultObj = (List<TTscClientPerson>) result
				.get("p_RECORDSET");

		if (resultObj != null && resultObj.size() > 0) {
			return resultObj.get(0);
		} else
			return null;
	}

	/**
	 * validateTrakheesIdAndPIN
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonDAO#validateTrakheesIdAndPIN(ae.pcfc.etks.online.common.vo.TrakheesIdAndPINVO)
	 * 
	 */
	public boolean validateTrakheesIdAndPIN(
			TrakheesIdAndPINVO trakheesIdAndPINVO) {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.SINGLE_CLIENT_VALIDATE_TKSID_PIN);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
		// issue with the driver
		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_TRAKHEES_ID",
				Types.VARCHAR), new SqlParameter("P_PIN_CODE", Types.VARCHAR),
				new SqlOutParameter("P_VALID", Types.VARCHAR));

		SqlParameterSource in = new MapSqlParameterSource().addValue(
				"P_TRAKHEES_ID", trakheesIdAndPINVO.getStrTrakheesID())
				.addValue("P_PIN_CODE", trakheesIdAndPINVO.getStrTrakheesPIN());

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		// String valid = ;
		return (result.get("P_VALID").toString()).equals("0") ? false : true;
	}

	/**
	 * getPinCodeByTrakheesId
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonDAO#getPinCodeByTrakheesId(java.lang.String)
	 * 
	 */
	public String getPinCodeByTrakheesId(String trakheesId) {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.GET_CLIENT_PIN_BY_TRAKHEES_ID);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_TRAKHEES_ID",
				Types.VARCHAR),
				new SqlOutParameter("P_PIN_CODE", Types.VARCHAR));

		SqlParameterSource in = new MapSqlParameterSource().addValue(
				"P_TRAKHEES_ID", trakheesId);

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);

		return result.get("P_PIN_CODE").toString();
	}

	/**
	 * findSCTrakheesIdsByRoleId
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonDAO#findSCTrakheesIdsByRoleId(java.lang.String)
	 * 
	 */
	public List<TrakheesIdVO> findSCTrakheesIdsByRoleId(String roleId) {
		@SuppressWarnings("deprecation")
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
				.withProcedureName(
						ConstantsCommon.GET_CLIENT_TRAKHEES_ID_BY_ROLE_ID)
				.returningResultSet(
						"p_RECORDSET",
						ParameterizedBeanPropertyRowMapper
								.newInstance(TrakheesIdVO.class));

		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver
		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_ROLE_ID",
				Types.VARCHAR));
		SqlParameterSource in = new MapSqlParameterSource().addValue(
				"P_ROLE_ID", roleId);
		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);

		@SuppressWarnings("unchecked")
		List<TrakheesIdVO> resultObj = (List<TrakheesIdVO>) result
				.get("p_RECORDSET");

		if (resultObj != null && resultObj.size() > 0) {
			return resultObj;
		} else
			return null;
	}

	/**
	 * validateClientName
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonDAO#validateClientName(ae.pcfc.etks.online.common.vo.ClientNameVO)
	 * 
	 */
	public boolean validateClientName(ClientNameVO clientNameVO) {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.SINGLE_CIENT_VALIDATE_CLIENT_NAME);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(new SqlParameter(
				"P_CLIENT_NAME_EN", Types.VARCHAR), new SqlParameter(
				"P_CLIENT_NAME_AR", Types.VARCHAR), new SqlOutParameter(
				"P_VALID", Types.VARCHAR));

		SqlParameterSource in = new MapSqlParameterSource().addValue(
				"P_CLIENT_NAME_EN", clientNameVO.getStrNameEng()).addValue(
				"P_CLIENT_NAME_AR", clientNameVO.getStrNameArb());

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);

		String string = (String) result.get("P_VALID");

		if (string.equals(ConstantsCommon.LICENSE_VALID))
			return true;
		else
			return false;
	}
	/**
	 * findTrkheesIdAndPinByEmail
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonDAO#findTrkheesIdAndPinByEmail(java.lang.String)
	 * 
	 */
	public TrakheesIdAndPINVO findTrkheesIdAndPinByEmail(String email) {

		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.GET_TAKHEES_ID_AND_PIN_BY_EMAIL).returningResultSet("P_RECORDSET", new RowMapper<TrakheesIdAndPINVO>() {
					public TrakheesIdAndPINVO mapRow(ResultSet rs, int arg1)
							throws SQLException {
						// TODO Auto-generated method stub
						TrakheesIdAndPINVO idPin = new TrakheesIdAndPINVO();
						idPin.setStrTrakheesID(rs.getString("P_TRAKHEES_ID"));
						idPin.setStrTrakheesPIN(rs.getString("P_PIN_CODE"));
						return idPin;
					}
				});
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_CC_EMAIL", Types.VARCHAR));

		SqlParameterSource in = new MapSqlParameterSource().addValue("P_CC_EMAIL", email);

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		@SuppressWarnings("unchecked")
		List<TrakheesIdAndPINVO> resultObj = (List<TrakheesIdAndPINVO>) result.get("P_RECORDSET");

		if (resultObj != null && resultObj.size() > 1) {
			throw new TscDuplicateClientEmailException(email);
		}
		else if (resultObj != null && resultObj.size() == 1) {
			return resultObj.get(0);
		}else{
			return null;
		}		

}

	/*public TrakheesIdAndPINVO findTrkheesIdAndPinByEmail(String email) {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.GET_TAKHEES_ID_AND_PIN_BY_EMAIL);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(new SqlOutParameter(
				"P_TRAKHEES_ID", Types.VARCHAR), new SqlOutParameter(
				"P_PIN_CODE", Types.VARCHAR), new SqlParameter("P_CC_EMAIL",
				Types.VARCHAR));

		SqlParameterSource in = new MapSqlParameterSource().addValue(
				"P_CC_EMAIL", email);

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);

		if (result.get("P_TRAKHEES_ID") != null
				&& result.get("P_PIN_CODE") != null) {
			TrakheesIdAndPINVO idAndPINVO = new TrakheesIdAndPINVO();

			idAndPINVO.setStrTrakheesID(result.get("P_TRAKHEES_ID").toString());
			idAndPINVO.setStrTrakheesPIN(result.get("P_PIN_CODE").toString());

			return idAndPINVO;
		} else
			return null;
	}*/

	/**
	 * getClientCategory
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonDAO#getClientCategory(java.lang.String)
	 * 
	 */
	@SuppressWarnings("unchecked")
	public List<TTscClientCategory> getClientCategory(String clientType) {

		Criteria criteria = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createCriteria(TTscClientCategory.class);

		if (clientType != null)
			criteria.add(Restrictions.eq("ctCategoryType", clientType));
		
		criteria.add(Restrictions.eq("ctStatus", ConstantsCommon.ACTIVE));

		return (List<TTscClientCategory>) criteria.list();
	}

	/**
	 * getClientCategoryById
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonDAO#getClientCategoryById(java.lang.String)
	 * 
	 */
	public TTscClientCategory getClientCategoryById(String categoryId) {

		Criteria criteria = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createCriteria(TTscClientCategory.class);

		criteria.add(Restrictions.eq("ctId", categoryId));
		criteria.add(Restrictions.eq("ctStatus", ConstantsCommon.ACTIVE));
		TTscClientCategory clientCategory = (TTscClientCategory) criteria
				.uniqueResult();

		return clientCategory;
	}

	/**
	 * getClientLicenseTypeByCode
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonDAO#getClientLicenseTypeByCode(java.lang.String)
	 * 
	 */
	public TTscLicType getClientLicenseTypeByCode(String licenseTypeCode) {

		Criteria criteria = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createCriteria(TTscLicType.class);

		criteria.add(Restrictions.eq("ltCode", licenseTypeCode));
		criteria.add(Restrictions.eq("ltStatus", ConstantsCommon.ACTIVE));
		TTscLicType licType = (TTscLicType) criteria.uniqueResult();

		return licType;

	}


	/**
	 * searchPerson
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonDAO#searchPerson(ae.pcfc.etks.online.common.vo.SearchCriteriaPersonVO)
	 * 
	 */
	public List<TTscClientPerson> searchPerson(SearchCriteriaPersonVO searchCriteriaPersonVO) {
		@SuppressWarnings("deprecation")
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
				.withProcedureName(ConstantsCommon.SINGLE_CLIENT_PERSON_SEARCH).returningResultSet("P_RECORDSET",
						ParameterizedBeanPropertyRowMapper.newInstance(TTscClientPerson.class));
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(
				new SqlParameter("P_CP_CLIENTID",Types.NUMERIC),
				new SqlParameter("P_CP_NAME", Types.VARCHAR), 
				new SqlParameter("P_CP_NAME_ARB", Types.VARCHAR),
				new SqlParameter("P_CP_HOME_TEL", Types.VARCHAR),
				new SqlParameter("P_CC_OFF_TEL", Types.VARCHAR),
				new SqlParameter("P_CP_MOBILE1", Types.VARCHAR),
				new SqlParameter("P_CP_TRAKHEES_ID", Types.VARCHAR),
				new SqlParameter("P_CP_CLIENTROLE", Types.VARCHAR));

		SqlParameterSource in = new MapSqlParameterSource().addValue("P_CP_CLIENTID", searchCriteriaPersonVO.getClientId())
				.addValue("P_CP_NAME", searchCriteriaPersonVO.getNameEng())
				.addValue("P_CP_NAME_ARB", searchCriteriaPersonVO.getNameArb())
				.addValue("P_CP_HOME_TEL", searchCriteriaPersonVO.getHomeNumber())
				.addValue("P_CC_OFF_TEL", searchCriteriaPersonVO.getOfficeNumber())
				.addValue("P_CP_MOBILE1", searchCriteriaPersonVO.getMobile())
				.addValue("P_CP_TRAKHEES_ID", searchCriteriaPersonVO.getTrakheesID())
				.addValue("P_CP_CLIENTROLE", searchCriteriaPersonVO.getClientRoleIdListAsString());

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		@SuppressWarnings("unchecked")
		List<TTscClientPerson> resultObj = (List<TTscClientPerson>) result.get("P_RECORDSET");

		if (resultObj != null && resultObj.size() > 0) {
			return resultObj;
		} else
			return null;	}

	public String updateClientPersonStatus(ClientPersonReq clientPersonReq,String strTrakheesId, String clientStatus) {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
		.withProcedureName(ConstantsCommon.SINGLE_CLIENT_PERSON_UPDATE_STATUS_PROC);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver
		
		jdbcCall = jdbcCall.declareParameters(new SqlParameter(
				"P_CP_TRAKHEES_ID", Types.VARCHAR),new SqlOutParameter("P_UPDATED", Types.VARCHAR));
		
		SqlParameterSource in = new MapSqlParameterSource()
				.addValue("P_CP_TRAKHEES_ID", strTrakheesId);

		
		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		return result.get("P_UPDATED").toString();

	}
}
